# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html

# useful for handling different item types with a single interface
from itemadapter import ItemAdapter
import pymysql
from laojiumen.settings import MYSQL_HOST, MYSQL_PORT, MYSQL_USER, MYSQL_PASSWORD, MYSQL_DB_NAME


class LaojiumenPipeline:
    def __init__(self):
        # 连接数据库
        self.db_conn = pymysql.connect(host=MYSQL_HOST,
                                       port=MYSQL_PORT,
                                       user=MYSQL_USER,
                                       passwd=MYSQL_PASSWORD,
                                       db=MYSQL_DB_NAME)
        # 获取数据库游标
        self.cursor = self.db_conn.cursor()
        self.cursor.execute("DROP TABLE IF EXISTS laojiumen;")
        self.cursor.execute(
            "CREATE TABLE IF NOT EXISTS laojiumen ( EP VARCHAR(255), url VARCHAR(255));"
        )

    def process_item(self, item, spider):
        # sql语句
        insert_sql = """
        INSERT INTO laojiumen (EP,url) VALUES (%s,%s)
        """
        # 插入数据到数据库
        self.cursor.execute(insert_sql, (item['EP'], item['url']))
        # 提交数据，否则无法保存到数据库
        self.db_conn.commit()

    def close_spider(self, spider):
        # 关闭游标和连接
        self.cursor.close()
        self.db_conn.close()
